Array Entered Formulas for Excel

Here is an example of creating a spreadsheet for the smfGetYahooPortfolioView function and array entering that formula into a spreadsheet in Excel.I am using Excel 2010 for this example, but it should work fine for any version of Excel.

 

Here you see my spreadsheet for YPV.The yellow column is the list of tickers for the formula to use and the yellow row is the items for it to retrieve.

In the example, I have 25 rows for tickers (B5:B34) and I have 18 columns for items (C3:T3)

You can (and usually will, especially for tickers) have more columns and rows than you have items or tickers.That way you can add more later without changing the formula.

 

In this example select the cells C4 to T34.

Then in the formula bar you enter this formula: =smfGetYahooPortfolioView(B5:B34,C3:T3,"v1",1), then press CTRL+SHIFT+ENTER and that will array enter that formula into that selection of cells!

 

To change or remove an array entered formula, you need to select all the cells containing the formula, hit delete and then either enter a new formula or just hint enter and it will be gone.If you want to change the size of the array, you have to delete the current on first, then it is the same as creating a new one above.

TIP!

To select an array entered formula, especially if you are not certain as to how big it is� find the first cell at the top right. Then use CTRL+SHFT+RIGHT (to select all of that row) and then while still holding CTRL+SHIFT press DOWN.That will now select all the rows of the array as well now.Now if you press Delete it will clear the array and you can create a new one.

Here is the example spreadsheet used above smfGetYahooPortfolioView-example.xlsx